package com.wgy.day41JDBC.封装表为java类;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
练习:定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回
分析:1.定义一个Emp类
    2.定义方法 public list<Emp> findAll(){}
    3.实现方法 select * from Emp
*/
public class Demo08JDBC {
    public static void main(String[] args) {
        List<Emp> list = new Demo08JDBC().findAll();
        System.out.println(list);
        System.out.println(list.size());
    }

    //定义一个方法,查询emp表的数据将其封装为对象,然后装载集合,返回
    public List<Emp> findAll(){
        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        List<Emp> list=null;
        try {//1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取连接
          conn = DriverManager.getConnection("jdbc:mysql:///db3","root","root");
            //3.定义Sql
            String sql="select * from emp";
            //4.获取执行sql的对象
         stmt = conn.createStatement();
            //5.执行sql
           rs = stmt.executeQuery(sql);
            //6遍历结果集,封装对象,装载集合
            Emp emp=null;
            //创建集合
            list =new ArrayList<Emp>();
            while (rs.next()){
                //获取数据
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int job_id = rs.getInt("job_id");
                int mgr = rs.getInt("mgr");
                Date joinDate = rs.getDate("joinDate");
                double salary = rs.getDouble("salary");
                double bonus = rs.getDouble("bonus");
                int dept_id = rs.getInt("dept_id");
                //创建emp对象
               emp=new Emp();
               //设置emp对象的值
                emp.setId(id);
                emp.setName(name);
                emp.setJob_id(job_id);
                emp.setMgr(mgr);
                emp.setJoinDate(joinDate);
                emp.setSalary(salary);
                emp.setBonus(bonus);
                emp.setDept_id(dept_id);

                list.add(emp);

            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(stmt!=null){
                try {
                    stmt.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }

        }

        return list ;
    }
}
